package com.ccsc.auth.cas.dao;

import javax.annotation.Resource;
import javax.sql.DataSource;

import com.ccsc.auth.cas.entity.*;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public class AccountDao {
	private static final String SQL_VERIFY_ACCOUNT = "select * from sdp_kmga_sys_user where user_account = ? and user_password = ? and user_status = 1";
	private static final String SQL_VERIFY_ACCOUNT_BY_TEL = "select * from sdp_kmga_sys_user where user_phone = ? and user_status = 1";
	private static final String SQL_GET_USER_BY_USER_ID = "select * from sdp_kmga_sys_user where user_id = ? and user_status = 1";
	private static final String SQL_GET_ROLE_BY_USERID = "select * from sdp_kmga_sys_rel_authorize a LEFT join sdp_kmga_sys_role b on a.role_id=b.role_id where a.user_id = ?";
	private static final String SQL_GET_RESOURCE_BY_ROLE_ID = "select * from sdp_kmga_sys_rel_permission a left join sdp_kmga_sys_resource b on a.resource_id=b.resource_id left join sdp_kmga_sys_system c on b.system_id=c.system_id where a.role_id = ?";
	private static final String SQL_GET_SYSTEMS = "select * from sdp_kmga_sys_system where system_status = 1 order by system_id";

	private static final String SQL_VERIFY_SYS_ACCOUNT = "select * from sdp_sys_user where account = ? and password = ? and status = 1";
	private static final String SQL_GET_SYS_USER_BY_USER_ID = "select * from sdp_sys_user where user_id = ? and status = 1";
	private static final String SQL_GET_SYS_USER_BY_USER_ACCOUNT = "select * from sdp_sys_user where account = ? and status = 1";
	private static final String SQL_GET_SYS_SYSTEMS = "select * from sdp_sys_system where system_id = ?";
	private static final String SQL_GET_ALL_SYS_SYSTEMS = "select * from sdp_sys_system";

	private JdbcTemplate jdbcTemplate;

	@Resource
	public void setDataSource(DataSource dataSource){
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	/**
	 * 验证用户名和密码是否正确
	 * @create 2015-10-29 下午3:34:45
	 * @author xingwen
	 */
	public UserEntity verifyAccount(String username, String password){
		try{
			UserEntity userEntity = this.jdbcTemplate.queryForObject(SQL_VERIFY_ACCOUNT, new Object[]{username, password}, new BeanPropertyRowMapper<UserEntity>(UserEntity.class));
			return userEntity;
		}catch(EmptyResultDataAccessException e){
			return null;
		}
	}

	public UserEntity verifyAccountByTel(String phoneNum){
		try{
			UserEntity userEntity = this.jdbcTemplate.queryForObject(SQL_VERIFY_ACCOUNT_BY_TEL, new Object[]{phoneNum}, new BeanPropertyRowMapper<UserEntity>(UserEntity.class));
			return userEntity;
		}catch(EmptyResultDataAccessException e){
			return null;
		}
	}

	public SysUserEntity verifySysAccount(String username, String password){
		try{
			SysUserEntity sysUserEntity = this.jdbcTemplate.queryForObject(SQL_VERIFY_SYS_ACCOUNT, new Object[]{username, password}, new BeanPropertyRowMapper<SysUserEntity>(SysUserEntity.class));
			return sysUserEntity;
		}catch(EmptyResultDataAccessException e){
			return null;
		}
	}

	public SysUserEntity getSysUserByUserId(String userId){
		try{
			SysUserEntity sysUserEntity = this.jdbcTemplate.queryForObject(SQL_GET_SYS_USER_BY_USER_ID, new Object[]{userId}, new BeanPropertyRowMapper<SysUserEntity>(SysUserEntity.class));
			return sysUserEntity;
		}catch(EmptyResultDataAccessException e){
			return null;
		}
	}

	public SysUserEntity getSysUserByUserName(String userName){
		try{
			SysUserEntity sysUserEntity = this.jdbcTemplate.queryForObject(SQL_GET_SYS_USER_BY_USER_ACCOUNT, new Object[]{userName}, new BeanPropertyRowMapper<SysUserEntity>(SysUserEntity.class));
			return sysUserEntity;
		}catch(EmptyResultDataAccessException e){
			return null;
		}
	}

	public SysSystemEntity getSysSystemBySystemId(String systemId){
		try{
			SysSystemEntity sysSystemEntity = this.jdbcTemplate.queryForObject(SQL_GET_SYS_SYSTEMS, new Object[]{systemId}, new BeanPropertyRowMapper<SysSystemEntity>(SysSystemEntity.class));
			return sysSystemEntity;
		}catch(EmptyResultDataAccessException e){
			return null;
		}
	}

	public List<SysSystemEntity> getSysSystems(){
		try {
			List<SysSystemEntity> list = this.jdbcTemplate.query(SQL_GET_ALL_SYS_SYSTEMS, new Object[]{}, new BeanPropertyRowMapper<SysSystemEntity>(SysSystemEntity.class));
			return list;
		}
		catch (Exception e)
		{
			return null;
		}
	}

	public UserEntity getUserByUserId(String userId){
		try{
			UserEntity userEntity = this.jdbcTemplate.queryForObject(SQL_GET_USER_BY_USER_ID, new Object[]{userId}, new BeanPropertyRowMapper<UserEntity>(UserEntity.class));
			return userEntity;
		}catch(EmptyResultDataAccessException e){
			return null;
		}
	}

	public List<RoleEntity> getRoleByUserId(Integer userId){
		try {
			List<RoleEntity> list = this.jdbcTemplate.query(SQL_GET_ROLE_BY_USERID, new Object[]{userId}, new BeanPropertyRowMapper<RoleEntity>(RoleEntity.class));
			return list;
		}
		catch (Exception e)
		{
			return null;
		}
	}

	public List<SystemEntity> getSystems(){
		try {
			List<SystemEntity> list = this.jdbcTemplate.query(SQL_GET_SYSTEMS, new Object[]{}, new BeanPropertyRowMapper<SystemEntity>(SystemEntity.class));
			return list;
		}
		catch (Exception e)
		{
			return null;
		}
	}

	public List<ResourceEntity> getResourceByRoleId(Integer roleId){
		try {
			List<ResourceEntity> list = this.jdbcTemplate.query(SQL_GET_RESOURCE_BY_ROLE_ID, new Object[]{roleId}, new BeanPropertyRowMapper<ResourceEntity>(ResourceEntity.class));
			return list;
		}
		catch (Exception e)
		{
			return null;
		}
	}
}